let mongoose = require('mongoose')
const {Schema} = require("mongoose");

const {county_sum} = require('../data_mock/county')
const {allCityData} = require('../data_mock/city_data')
const {allProvinceData} = require('../data_mock/province_data')

mongoose.set('strictQuery', true);
mongoose.connect("mongodb://127.0.0.1/epi_thesis")
mongoose.connection.once("open",function (){
    console.log("数据库连接成功~~~")
})

let countySchema = new Schema({
    county:{//县名
        type:String,
        required: true
    },
    city_code:{//所属市的code
        type:Number,
        required: true
    },
    year:{//销售年份
        type:String,
        required: true
    },
    platform_classification:{//平台分类
        type:Schema.Types.Mixed,
        required: true
    },
    sales:{
        type:Schema.Types.Mixed,
        required:true
    },
    level:{
        type:Number,
        required:true
    }
})

// let citySchema = new Schema({
//     city:{//县名
//         type:String,
//         required: true
//     },
//     city_code:{//所属市的code
//         type:Number,
//         required: true
//     },
//     province_code:{//自身的code
//         type:Number,
//         required: true
//     },
//     year:{//销售年份
//         type:String,
//         required: true
//     },
//     platform_classification:{//平台分类
//         type:Schema.Types.Mixed,
//         required: true
//     },
//     sales:{
//         type:Schema.Types.Mixed,
//         required:true
//     },
//     level:{
//         type:Number,
//         required:true
//     }
// })
//
// let provinceSchema = new Schema({
//     city:{//省名
//         type:String,
//         required: true
//     },
//     province_code:{//自身的code
//         type:Number,
//         required: true
//     },
//     year:{//销售年份
//         type:String,
//         required: true
//     },
//     platform_classification:{//平台分类
//         type:Schema.Types.Mixed,
//         required: true
//     },
//     sales:{
//         type:Schema.Types.Mixed,
//         required:true
//     },
//     level:{
//         type:Number,
//         required:true
//     }
// })

//定义模型
let countyModel = mongoose.model("county_year",countySchema,"county_year")
// let cityModel = mongoose.model("city_year",citySchema,"city_year")
// let provinceModel = mongoose.model("province_year",provinceSchema,"province_year")

//操作数据库的方法（在router_handler中调用）

//mock销量的数据
// const test = async () => {
    //每个县每年的数据
    /*for (let i = 0; i < county_sum.length;) {
        try{
            await countyModel.insertMany(county_sum.slice(i,i + 10))
            if (i+10 < county_sum.length){
                i += 10
            }else {
                i = county_sum.length
            }
            console.log(`数据插入中--------${i}/${county_sum.length}`)
        }catch (e) {

        }
    }*/

    //计算每个市每年的数据
    /*for (let i = 0; i < allCityData.length;i++) {
        let sales_red_sum  = 0
        let sales_green_sum  = 0
        let sales_other_sum  = 0

        let male_sum = 0

        let jd_red_sum = 0
        let jd_green_sum = 0
        let jd_other_sum = 0
        let tb_red_sum = 0
        let tb_green_sum = 0
        let tb_other_sum = 0
        let pdd_red_sum = 0
        let pdd_green_sum = 0
        let pdd_other_sum = 0
        let hm_red_sum = 0
        let hm_green_sum = 0
        let hm_other_sum = 0
        let mt_red_sum = 0
        let mt_green_sum = 0
        let mt_other_sum = 0

        let age_18 = 0
        let age_28 = 0
        let age_38 = 0
        let age_48 = 0
        try{
            await countyModel.find({city_code: allCityData[i].code , year: '2022年'})
                .then(res=>{
                    for (let i = 0; i < res.length; i++) {
                        sales_red_sum += res[i].sales.sales_red_sum
                        sales_green_sum += res[i].sales.sales_green_sum
                        sales_other_sum += res[i].sales.sales_other_sum

                        male_sum += res[i].platform_classification.gender.male

                        jd_red_sum += res[i].platform_classification.platform_sales[0].京东.red_shell_sales
                        jd_green_sum += res[i].platform_classification.platform_sales[0].京东.green_shell_sales
                        jd_other_sum += res[i].platform_classification.platform_sales[0].京东.other_shell_sales
                        tb_red_sum += res[i].platform_classification.platform_sales[1].淘宝.red_shell_sales
                        tb_green_sum += res[i].platform_classification.platform_sales[1].淘宝.green_shell_sales
                        tb_other_sum += res[i].platform_classification.platform_sales[1].淘宝.other_shell_sales
                        pdd_red_sum += res[i].platform_classification.platform_sales[2].拼多多.red_shell_sales
                        pdd_green_sum += res[i].platform_classification.platform_sales[2].拼多多.green_shell_sales
                        pdd_other_sum += res[i].platform_classification.platform_sales[2].拼多多.other_shell_sales
                        hm_red_sum += res[i].platform_classification.platform_sales[3].盒马.red_shell_sales
                        hm_green_sum += res[i].platform_classification.platform_sales[3].盒马.green_shell_sales
                        hm_other_sum += res[i].platform_classification.platform_sales[3].盒马.other_shell_sales
                        mt_red_sum += res[i].platform_classification.platform_sales[4].美团.red_shell_sales
                        mt_green_sum += res[i].platform_classification.platform_sales[4].美团.green_shell_sales
                        mt_other_sum += res[i].platform_classification.platform_sales[4].美团.other_shell_sales

                        age_18 += Object.values(res[i].platform_classification.age)[0]*1
                        age_28 += Object.values(res[i].platform_classification.age)[1]*1
                        age_38 += Object.values(res[i].platform_classification.age)[2]*1
                        age_48 += Object.values(res[i].platform_classification.age)[3]*1
                    }

                    try{
                        cityModel.insertMany([
                            {
                                city:allCityData[i].name,
                                city_code:res[0].city_code,
                                province_code:allCityData[i].province_code,
                                year:'2022年',
                                platform_classification:{
                                    "platform_sales": [
                                        {
                                            "京东": {
                                                'red_shell_sales':jd_red_sum,
                                                'green_shell_sales':jd_green_sum,
                                                'other_shell_sales':jd_other_sum
                                            }
                                        },
                                        {
                                            "淘宝": {
                                                'red_shell_sales':tb_red_sum,
                                                'green_shell_sales':tb_green_sum,
                                                'other_shell_sales':tb_other_sum
                                            }
                                        },
                                        {
                                            "拼多多": {
                                                'red_shell_sales':pdd_red_sum,
                                                'green_shell_sales':pdd_green_sum,
                                                'other_shell_sales':pdd_other_sum
                                            }
                                        },
                                        {
                                            "盒马": {
                                                'red_shell_sales':hm_red_sum,
                                                'green_shell_sales':hm_green_sum,
                                                'other_shell_sales':hm_other_sum
                                            }
                                        },
                                        {
                                            "美团": {
                                                'red_shell_sales':mt_red_sum,
                                                'green_shell_sales':mt_green_sum,
                                                'other_shell_sales':mt_other_sum
                                            }
                                        },
                                    ],
                                    "gender": {
                                        "male": Math.round(male_sum/res.length),
                                        "female": 100-Math.round(male_sum/res.length)
                                    },
                                    "age": {
                                        "18-28": Math.round(age_18/res.length),
                                        "28-38": Math.round(age_28/res.length),
                                        "38-48": Math.round(age_38/res.length),
                                        "48-58": Math.round(age_48/res.length),
                                        "其他": 100 - Math.round(age_18/res.length)- Math.round(age_28/res.length)- Math.round(age_38/res.length)- Math.round(age_48/res.length)
                                    }
                                },
                                "sales": {
                                    "sales_red_sum": sales_red_sum,
                                    "sales_green_sum": sales_green_sum,
                                    "sales_other_sum": sales_green_sum
                                },
                                level:2
                            }
                        ])
                    }catch (e) {

                    }
                    return res
                })
                .catch(err=>{
                    return false
                })
            console.log(`数据插入中--------${i}/${allCityData.length}`)
        }catch (e) {

        }
    }*/

    //计算每个省的数据
    /*for (let i = 0; i < allProvinceData.length; i++) {
        let sales_red_sum  = 0
        let sales_green_sum  = 0
        let sales_other_sum  = 0

        let male_sum = 0

        let jd_red_sum = 0
        let jd_green_sum = 0
        let jd_other_sum = 0
        let tb_red_sum = 0
        let tb_green_sum = 0
        let tb_other_sum = 0
        let pdd_red_sum = 0
        let pdd_green_sum = 0
        let pdd_other_sum = 0
        let hm_red_sum = 0
        let hm_green_sum = 0
        let hm_other_sum = 0
        let mt_red_sum = 0
        let mt_green_sum = 0
        let mt_other_sum = 0

        let age_18 = 0
        let age_28 = 0
        let age_38 = 0
        let age_48 = 0
        try{
            await cityModel.find({province_code: allProvinceData[i].code , year: '2022年'})
                .then(res=>{
                    for (let i = 0; i < res.length; i++) {
                        sales_red_sum += res[i].sales.sales_red_sum
                        sales_green_sum += res[i].sales.sales_green_sum
                        sales_other_sum += res[i].sales.sales_other_sum

                        male_sum += res[i].platform_classification.gender.male

                        jd_red_sum += res[i].platform_classification.platform_sales[0].京东.red_shell_sales
                        jd_green_sum += res[i].platform_classification.platform_sales[0].京东.green_shell_sales
                        jd_other_sum += res[i].platform_classification.platform_sales[0].京东.other_shell_sales
                        tb_red_sum += res[i].platform_classification.platform_sales[1].淘宝.red_shell_sales
                        tb_green_sum += res[i].platform_classification.platform_sales[1].淘宝.green_shell_sales
                        tb_other_sum += res[i].platform_classification.platform_sales[1].淘宝.other_shell_sales
                        pdd_red_sum += res[i].platform_classification.platform_sales[2].拼多多.red_shell_sales
                        pdd_green_sum += res[i].platform_classification.platform_sales[2].拼多多.green_shell_sales
                        pdd_other_sum += res[i].platform_classification.platform_sales[2].拼多多.other_shell_sales
                        hm_red_sum += res[i].platform_classification.platform_sales[3].盒马.red_shell_sales
                        hm_green_sum += res[i].platform_classification.platform_sales[3].盒马.green_shell_sales
                        hm_other_sum += res[i].platform_classification.platform_sales[3].盒马.other_shell_sales
                        mt_red_sum += res[i].platform_classification.platform_sales[4].美团.red_shell_sales
                        mt_green_sum += res[i].platform_classification.platform_sales[4].美团.green_shell_sales
                        mt_other_sum += res[i].platform_classification.platform_sales[4].美团.other_shell_sales

                        age_18 += Object.values(res[i].platform_classification.age)[0]*1
                        age_28 += Object.values(res[i].platform_classification.age)[1]*1
                        age_38 += Object.values(res[i].platform_classification.age)[2]*1
                        age_48 += Object.values(res[i].platform_classification.age)[3]*1
                    }

                    try{
                        provinceModel.insertMany([
                            {
                                city:allProvinceData[i].name,
                                province_code:allProvinceData[i].code,
                                year:'2022年',
                                platform_classification:{
                                    "platform_sales": [
                                        {
                                            "京东": {
                                                'red_shell_sales':jd_red_sum,
                                                'green_shell_sales':jd_green_sum,
                                                'other_shell_sales':jd_other_sum
                                            }
                                        },
                                        {
                                            "淘宝": {
                                                'red_shell_sales':tb_red_sum,
                                                'green_shell_sales':tb_green_sum,
                                                'other_shell_sales':tb_other_sum
                                            }
                                        },
                                        {
                                            "拼多多": {
                                                'red_shell_sales':pdd_red_sum,
                                                'green_shell_sales':pdd_green_sum,
                                                'other_shell_sales':pdd_other_sum
                                            }
                                        },
                                        {
                                            "盒马": {
                                                'red_shell_sales':hm_red_sum,
                                                'green_shell_sales':hm_green_sum,
                                                'other_shell_sales':hm_other_sum
                                            }
                                        },
                                        {
                                            "美团": {
                                                'red_shell_sales':mt_red_sum,
                                                'green_shell_sales':mt_green_sum,
                                                'other_shell_sales':mt_other_sum
                                            }
                                        },
                                    ],
                                    "gender": {
                                        "male": Math.round(male_sum/res.length),
                                        "female": 100-Math.round(male_sum/res.length)
                                    },
                                    "age": {
                                        "18-28": Math.round(age_18/res.length),
                                        "28-38": Math.round(age_28/res.length),
                                        "38-48": Math.round(age_38/res.length),
                                        "48-58": Math.round(age_48/res.length),
                                        "其他": 100 - Math.round(age_18/res.length)- Math.round(age_28/res.length)- Math.round(age_38/res.length)- Math.round(age_48/res.length)
                                    }
                                },
                                "sales": {
                                    "sales_red_sum": sales_red_sum,
                                    "sales_green_sum": sales_green_sum,
                                    "sales_other_sum": sales_other_sum
                                },
                                level:allProvinceData[i].level
                            }
                        ])
                    }catch (e) {

                    }
                    return res
                })
                .catch(err=>{
                    return false
                })
            console.log(`数据插入中--------${i}/${allProvinceData.length}`)
        }catch (e) {

        }
    }
}*/
// }
//查询全部省数据

//点击地图查询省份数据
const getDataOfOnclick = (county) => {
    return countyModel.find({county:county})
        .then(res=>{
            return JSON.parse(JSON.stringify(res)).sort((a,b)=>{
                return a.year.slice(0,4) - b.year.slice(0,4)
            })
        })
        .catch(err=>{
            console.log(err)
            return false
        })
}

//热门平台圆饼图数据(三级城市)
const getProductPieOfCounty = async (county) => {
    let classification = await getDataOfOnclick(county)
    //热门平台销量图表数据
    let productTotalSales = []
    classification.map(item => {
        let products = []
        let productName = []
        item.platform_classification.platform_sales.map(product => {
            let sales = Object.values(...Object.values(product))
            let sum = 0
            sales.map(values => {
                sum += +values
            })
            products.push(sum)
            productName.push(...Object.keys(product))
        })
        // let product = ['京东','淘宝','拼多多','盒马','美团']
        productTotalSales.push({year:item.year,city:item.county,products,productName})
    })
    return productTotalSales
}

module.exports = {countyModel, getDataOfOnclick,getProductPieOfCounty}
